package database;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;

import run.Worker;

import data.Game;
import data.Player;

public class DBHandle
{
	Connection	conn;

	public boolean makeConnection()
	{
		Worker.GetLogger()
				.log(Level.INFO, "Attempting to connect to database.");
		try
		{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
					.newInstance();
			conn = DriverManager
					.getConnection("jdbc:sqlserver://localhost; user=bliu");
			Worker.GetLogger().log(Level.INFO,
					"Success: connected to database.");
			return true;
		} catch (Exception e)
		{
			Worker.GetLogger().log(Level.SEVERE,
					"Failed to connect to database. " + e.toString());
			return false;
		}
	}

	public boolean disconnect()
	{
		try
		{
			conn.close();
			Worker.GetLogger().log(Level.INFO,
					"Success: Disconnected from database.");
			return true;
		} catch (SQLException e)
		{
			Worker.GetLogger().log(Level.SEVERE,
					"Failed to disconnect from database. " + e.toString());
			return false;
		}
	}

	@SuppressWarnings("deprecation")
	public boolean insertGames(Player player)
	{
		String sql = "INSERT INTO [Players].[dbo].[Games] ([Name],[Number],[Position],[Team],"
				+ "[Date],[Opponent],[Score],[GS],[Min],[FGM],[FGA]"
				+ ",[FGPcnt],[ThreePtM],[ThreePtA],[ThreePtPcnt],[FTA],[FTM]"
				+ ",[FTPcnt],[RebOff],[RebDef],[RebTot],[Ast],[Turnover],[Stl],[Blk],[PF],[Pts],[LastUpdate])"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement s = null;
		try
		{
			s = conn.prepareStatement(sql);
			for (int i = 0; i < player.getGames().size(); i++)
			{
				Game game = player.getGames().get(i);
				s.setString(1, player.getName());
				s.setInt(2, Integer.parseInt(player.getNumber()));
				s.setString(3, player.getPosition());
				s.setString(4, player.getTeam());
				DateFormat formatter = new SimpleDateFormat("MMM dd");
			    try
				{
			    	java.util.Date gameDate = (java.util.Date)formatter.parse(game.getDate());
			    	gameDate.setYear(110); //year 2010
			    	java.sql.Date sqlGameDate = new java.sql.Date(gameDate.getTime());
			    	s.setDate(5, sqlGameDate);
				} catch (ParseException e)
				{
					Worker.GetLogger().log(
							Level.SEVERE,
							"Error: Failed parsing date: " + game.getDate());
				}
			    
				//s.setString(5, game.getDate());
				s.setString(6, game.getOpponent());
				s.setString(7, game.getScore());
				s.setString(8, game.getGs());
				s.setString(9, game.getMin());
				s.setInt(10, Integer.parseInt(game.getFgm()));
				s.setInt(11, Integer.parseInt(game.getFga()));
				if (game.getFgPcnt().equals("N/A"))
					s.setString(12, null);
				else
					s.setFloat(12, Float.parseFloat(game.getFgPcnt()));
				s.setInt(13, Integer.parseInt(game.getThreePtM()));
				s.setInt(14, Integer.parseInt(game.getThreePtA()));
				if (game.getThreePtPcnt().equals("N/A"))
					s.setString(15, null);
				else
					s.setFloat(15, Float.parseFloat(game.getThreePtPcnt()));
				s.setInt(16, Integer.parseInt(game.getFtm()));
				s.setInt(17, Integer.parseInt(game.getFta()));
				if (game.getFtPcnt().equals("N/A"))
					s.setString(18, null);
				else
					s.setFloat(18, Float.parseFloat(game.getFtPcnt()));
				s.setInt(19, Integer.parseInt(game.getoReb()));
				s.setInt(20, Integer.parseInt(game.getdReb()));
				s.setInt(21, Integer.parseInt(game.getTotReb()));
				s.setInt(22, Integer.parseInt(game.getAst()));
				s.setInt(23, Integer.parseInt(game.getTo()));
				s.setInt(24, Integer.parseInt(game.getStl()));
				s.setInt(25, Integer.parseInt(game.getBlk()));
				s.setInt(26, Integer.parseInt(game.getPf()));
				s.setInt(27, Integer.parseInt(game.getPts()));
				
				java.util.Date now = new java.util.Date();
				java.sql.Date sqlDate = new java.sql.Date(now.getTime());
				s.setDate(28,sqlDate);
				s.addBatch();
			}
			int[] rows = s.executeBatch();
			conn.commit();
			Worker.GetLogger().log(
					Level.INFO,
					"Success: Inserted " + Integer.toString(rows.length)
							+ " into database.");
			return true;
		} catch (SQLException e)
		{
			Worker.GetLogger().log(Level.SEVERE,
					"Failed to insert games into database. " + e.toString());
			return false;
		}
	}

}